﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;

namespace ScrumProject.Models
{
    public class RealizedWorkRepository : IDisposable
    {
        SMRPO6Context db = new SMRPO6Context();
        public void AddRealizedWork(RealizedWork realizedWork)
        {

            using (var db = new SMRPO6Context())
            {

                db.Database.ExecuteSqlCommand("exec dbo.InsertRealisedWork @DateOfWorking,@TaskId,@WorkRemaining,@UserId",
                                              new SqlParameter("DateOfWorking", realizedWork.DateOfWorking),
                                              new SqlParameter("TaskId", realizedWork.TaskId),
                                              new SqlParameter("WorkRemaining", realizedWork.WorkRemaining),
                                              new SqlParameter("UserId", realizedWork.UserId));

                db.SaveChanges();

            }

        }
       
        
        
        public void UpdateRealizedWork(RealizedWork realizedWork)
        {

            using (var db = new SMRPO6Context())
            {

                db.Database.ExecuteSqlCommand("exec dbo.UpdateRealisedWork @Id,@WorkDone,@WorkRemaining,@UserId,@TaskId,@DateOfWorking",
                                              new SqlParameter("Id", realizedWork.Id),
                                              new SqlParameter("WorkDone", realizedWork.WorkDone),
                                              new SqlParameter("WorkRemaining", realizedWork.WorkRemaining),
                                              new SqlParameter("UserId", realizedWork.UserId),
                                              new SqlParameter("TaskId", realizedWork.TaskId),
                                               new SqlParameter("DateOfWorking", realizedWork.DateOfWorking));

                db.SaveChanges();

            }

        }

        public void UpdateRealizedWorkTime(int id, double work,int userId, double? WorkRemaining=0.0)
        {
            using (var db = new SMRPO6Context())
            {

                db.Database.ExecuteSqlCommand("exec dbo.UpdateRealisedWork @Id,@WorkDone,@WorkRemaining,@UserId",
                                              new SqlParameter("Id", id),
                                              new SqlParameter("WorkDone", work),
                                              new SqlParameter("WorkRemaining", WorkRemaining),
                                              new SqlParameter("UserId", userId));

                db.SaveChanges();

            }
        }

        public IEnumerable<RealizedWork> getRealizedWorksByTaskAndUser(int taskid, int userid)
        {
            return from RealizedWork in db.RealizedWorks
                   where RealizedWork.TaskId == taskid && RealizedWork.UserId == userid
                   select RealizedWork;
        }

        public Boolean checkDate(RealizedWork rw) { 
        
            if (DateTime.Compare(rw.DateOfWorking,DateTime.Now)<0){
            
                return true;
            }
            return false;
        }

        public IEnumerable<RealizedWork> getRealizedWorksByTask(int id) {

            return from RealizedWork in db.RealizedWorks
                   where RealizedWork.TaskId == id
                   select RealizedWork;
        }

        public IEnumerable<DateTime> getDates(int id)
        {

            return from RealizedWork in db.RealizedWorks
                   where RealizedWork.TaskId == id
                   select RealizedWork.DateOfWorking;
        }

        public RealizedWork getRealizedWorkByTask(int id,DateTime sd)
        {

            return (from RealizedWork in db.RealizedWorks
                    where RealizedWork.TaskId == id &&  DateTime.Compare(RealizedWork.DateOfWorking,sd) == 0
                   select RealizedWork).FirstOrDefault();
        }
        public double? getRealizedWorksByTask(int id, DateTime sd)
        {

            return (from RealizedWork in db.RealizedWorks
                    where RealizedWork.TaskId == id
                    select RealizedWork).Distinct().Sum(rw => rw.WorkRemaining);
        }

        public void Save()
        {
            db.SaveChanges();
        }

        private bool disposed = false;

        protected virtual void Dispose(bool disposing)
        {
            if (!this.disposed)
            {
                if (disposing)
                {
                    db.Dispose();
                }
            }
            this.disposed = true;
        }

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }
    }
}